// database/users.ts
import { ipcMain } from 'electron';
import sqlite3, { Database } from "sqlite3";
import path from "node:path";
import { faker } from "@faker-js/faker";
import {UserRole} from "@/type/role";
let usersDB: Database;



export interface User {
    id: string;
    username: string;
    password: string;
    email: string;
    avatar?: string;
    role: UserRole | string; // string when stored in DB, UserRole when parsed
    permissions: string[] | string; // string when stored in DB, string[] when parsed
    createdAt: string;
    updatedAt: string;
    lastLogin?: string;
    name?: string;
    phone?: string;
    address?: string;
    city?: string;
    code?: string;
    about?: string;
    isActive?: boolean;
}

function initUsersDatabase(): void {
    const dbPath = path.join(process.cwd(), 'database','users.db');


    usersDB = new sqlite3.Database(dbPath, (err: Error | null) => {
        if (err) {
            console.error('Users database opening error:', err.message);
            return;
        }

        console.log('Users database connected successfully');

        usersDB.run(
            `CREATE TABLE IF NOT EXISTS users (
                id TEXT PRIMARY KEY,
                username TEXT UNIQUE NOT NULL,
                password TEXT NOT NULL,
                email TEXT UNIQUE NOT NULL,
                avatar TEXT,
                role TEXT NOT NULL,
                permissions TEXT NOT NULL,
                createdAt DATETIME NOT NULL,
                updatedAt DATETIME NOT NULL,
                name TEXT,
                phone TEXT,
                address TEXT,
                city TEXT,
                code TEXT,
                about TEXT,
                isActive BOOLEAN DEFAULT 1,
                lastLogin DATETIME
            )`,
            (createTableErr: Error | null) => {
                if (createTableErr) {
                    console.error('Users table creation error:', createTableErr.message);
                } else {
                    console.log('Table "users" created/verified');
                }
            }
        );
    });
}


// 首先定义数据库行的类型
interface UserRow {
    id: string;
    username: string;
    password: string;
    email?: string;
    avatar?: string; // JSON字符串
    role?: string; // JSON字符串
    permissions?: string; // JSON字符串
    createdAt?: string;
    updatedAt?: string;
    name?: string;
    phone?: string;
    address?: string;
    city?: string;
    code?: string;
    about?: string;
    isActive: number; // SQLite中使用0/1表示布尔值
}
function insertRandomUser() {
    console.log('[1/3] Generating random user...');

    // 生成随机用户数据
    const randomUser: User = {
        id: faker.string.uuid(),
        username: faker.internet.userName(),  // 每次生成随机用户名
        password: faker.internet.password(),
        email: faker.internet.email(),
        avatar: faker.image.avatar(),
        role: {
            name: faker.helpers.arrayElement(['admin', 'doctor', 'nurse', 'user']),
            permission: faker.helpers.arrayElements(['read', 'write', 'delete', 'admin'], { min: 1, max: 4 })
        },
        permissions: faker.helpers.arrayElements(['read', 'write', 'delete', 'admin'], { min: 1, max: 4 }),
        createdAt: faker.date.past().toISOString(),
        updatedAt: faker.date.recent().toISOString(),
        name: faker.person.fullName(),
        phone: faker.phone.number(),
        address: faker.location.streetAddress(),
        city: faker.location.city(),
        code: faker.location.zipCode(),
        about: faker.lorem.paragraphs(1),
        isActive: faker.datatype.boolean()
    };

    console.log('[2/3] Generated user:', JSON.stringify({
        ...randomUser,
        password: '******',
        avatar: '[...base64...]'
    }, null, 2));

    // 序列化为数据库格式
    const dbUser = {
        ...randomUser,
        avatar: randomUser.avatar ? JSON.stringify(randomUser.avatar) : null,
        role: JSON.stringify(randomUser.role),
        permissions: JSON.stringify(randomUser.permissions),
        isActive: randomUser.isActive ? 1 : 0
    };

    // 插入数据库
    const sql = `INSERT INTO users (
        id, username, password, email, avatar, role, permissions,
        createdAt, updatedAt, name, phone, address, city, code, about, isActive
    ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

    const params = [
        dbUser.id, dbUser.username, dbUser.password, dbUser.email,
        dbUser.avatar, dbUser.role, dbUser.permissions,
        dbUser.createdAt, dbUser.updatedAt, dbUser.name,
        dbUser.phone, dbUser.address, dbUser.city, dbUser.code,
        dbUser.about, dbUser.isActive
    ];

    usersDB.run(sql, params, function(err) {
        if (err) {
            console.error('[3/3] Failed to insert user:', err.message);
        } else {
            console.log(`[3/3] User inserted successfully! Username: ${randomUser.username}, ID: ${randomUser.id}`);
        }
    });
}


// CRUD Operations for Users
ipcMain.handle('create-user', async (event, user: User) => {
    return new Promise((resolve, reject) => {
        try {
            // Validate required fields
            if (!user.username || !user.email || !user.password) {
                throw new Error('Username, email, and password are required');
            }

            // Prepare user data with defaults
            const userData = {
                id: user.id || crypto.randomUUID(),
                username: user.username.trim(),
                password: user.password, // Note: Should be hashed in production
                email: user.email.trim(),
                avatar: user.avatar || '',
                role: user.role || { name: 'user', permission: [] },
                permissions: user.permissions || [],
                name: user.name || '',
                phone: user.phone || '',
                address: user.address || '',
                city: user.city || '',
                code: user.code || '',
                about: user.about || '',
                isActive: user.isActive !== undefined ? user.isActive : true,
                createdAt: new Date().toISOString(),
                updatedAt: new Date().toISOString()
            };

            const sql = `INSERT INTO users (
                id, username, password, email, avatar, 
                role, permissions, createdAt, updatedAt, 
                name, phone, address, city, code, about, isActive
            ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`;

            const params = [
                userData.id,
                userData.username,
                userData.password,
                userData.email,
                userData.avatar,
                JSON.stringify(userData.role),
                JSON.stringify(userData.permissions),
                userData.createdAt,
                userData.updatedAt,
                userData.name,
                userData.phone,
                userData.address,
                userData.city,
                userData.code,
                userData.about,
                userData.isActive ? 1 : 0
            ];

            usersDB.run(sql, params, function(err) {
                if (err) {
                    console.error('Error creating user:', err.message);

                    // Handle specific SQLite errors
                    if (err.message.includes('UNIQUE constraint failed')) {
                        if (err.message.includes('username')) {
                            reject(new Error('Username already exists'));
                        } else if (err.message.includes('email')) {
                            reject(new Error('Email already exists'));
                        } else {
                            reject(new Error('Unique constraint violation'));
                        }
                    } else {
                        reject(new Error('Failed to create user'));
                    }
                    return;
                }

                // Return the created user ID and success status
                resolve({
                    id: userData.id,
                    success: true,
                    changes: this.changes
                });
            });
        } catch (error) {
            console.error('Unexpected error in create-user handler:', error);
            reject(new Error('Internal server error'));
        }
    });
});


ipcMain.handle('read-users', async () => {
    return new Promise((resolve, reject) => {
        const sql = `SELECT * FROM users ORDER BY createdAt DESC`;

        usersDB.all(sql, [], (err, rows) => {
            if (err) {
                console.error('用户查询失败:', err);
                reject(new Error('数据库查询失败'));
                return;
            }
            // 添加 resolve 语句
            resolve(rows.map(parseUserFromDb));
        });
    });
});



ipcMain.handle('update-user', async (event, user) => {
    return new Promise((resolve, reject) => {
        const sql = `UPDATE users
                    SET username = ?, email = ?, avatar = ?,
                        role = ?, permissions = ?, updatedAt = ?,
                        name = ?, phone = ?, address = ?, city = ?,
                        code = ?, about = ?, isActive = ?
                    WHERE id = ?`;

        const params = [
            user.username,
            user.email,
            user.avatar,
            JSON.stringify(user.role),
            JSON.stringify(user.permissions),
            new Date().toISOString(),
            user.name,
            user.phone,
            user.address,
            user.city,
            user.code,
            user.about,
            user.isActive,
            user.id
        ];

        usersDB.run(sql, params, (err) => {
            if (err) reject(err);
            resolve(true);
        });
    });
});


ipcMain.handle('delete-user', async (event, id) => {
    return new Promise((resolve, reject) => {
        const sql = 'DELETE FROM users WHERE id = ?';
        usersDB.run(sql, [id], (err) => {
            if (err) reject(err);
            resolve(true);
        });
    });
});

ipcMain.handle('find-user-by-username', async (event, username) => {
    return new Promise((resolve, reject) => {
        usersDB.get("SELECT * FROM users WHERE username = ?", [username], (err, row) => {
            if (err) reject(err);
            resolve(row);
        });
    });
});

ipcMain.handle('search-users', async (event, keyword) => {
    return new Promise((resolve, reject) => {
        const searchTerm = `%${keyword}%`;
        usersDB.all(
            `SELECT * FROM users
             WHERE username LIKE ? OR email LIKE ? OR name LIKE ?`,
            [searchTerm, searchTerm, searchTerm],
            (err, rows) => {
                if (err) reject(err);
                resolve(rows);
            }
        );
    });
});

// Add this helper function
function parseUserFromDb(dbUser: any): User {
    return {
        ...dbUser,
        role: tryParseJson(dbUser.role),
        permissions: tryParseJson(dbUser.permissions),
        avatar: tryParseJson(dbUser.avatar) || dbUser.avatar, // Try to parse as UserAvatar, fallback to string
        isActive: Boolean(dbUser.isActive)
    };
}

function tryParseJson(str: string): any {
    try {
        return JSON.parse(str);
    } catch {
        return str;
    }
}

export { initUsersDatabase,insertRandomUser, usersDB };
